package carassius.DAL;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;

/**
 *
 * @author siebz0r
 */
public class PictureTable
		extends Table<PictureRow, Integer>
{
	private final String SQL_ADD = "INSERT INTO picture VALUES ( NULL, ? , ? , ?)",
			SQL_DELETE = "DELETE FROM picture WHERE id = ?",
			SQL_GET_ALL = "SELECT * FROM picture",
			SQL_GET_BY_ID = "SELECT * FROM picture WHERE id = ?",
			SQL_GET_BY_PATH = "SELECT * FROM picture WHERE path = ?";
	private boolean _allCached;

	//<editor-fold defaultstate="collapsed" desc="Public Methods">
	//<editor-fold defaultstate="collapsed" desc="SQL add">
	public PictureRow add(String path, String name, String comment) throws SQLException
	{
		Integer id = add(new Object[]
				{
					path, name, comment
				});
		if (id >= 0)
		{
			return getByPrimaryKey(id);
		}
		return null;
	}

	@Override
	public Integer add(Object[] args) throws SQLException
	{
		Integer out = -1;
		if (args != null
				&& args.length >= 3
				&& args[0] instanceof String
				&& !((String) args[0]).isEmpty())
		{
			PreparedStatement st = Database.prepareStatement(SQL_ADD);
			st.setObject(1, ((String) args[0]).trim(), Types.VARCHAR); //path
			st.setObject(2, (args[1] != null) ? ((String) args[1]).trim() : null, java.sql.Types.VARCHAR); //name
			st.setObject(3, (args[2] != null) ? ((String) args[2]).trim() : null, java.sql.Types.VARCHAR); //comment

			synchronized (getRows())
			{
				try
				{
					if (st.executeUpdate() == 1)
					{
						ResultSet keys = st.getGeneratedKeys();
						if (keys.next())
						{
							int id = keys.getInt(1);
							if (!keys.wasNull())
							{
								out = id;
								_allCached = false;
								getByPrimaryKey(out);
								_allCached = true;
							}
						}
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return out;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL delete">
	@Override
	public boolean delete(Integer id) throws SQLException
	{
		boolean result = false;
		if (id != null
				&& id >= 0)
		{
			PreparedStatement st = Database.prepareStatement(SQL_DELETE);
			st.setInt(1, id);

			synchronized (getRows())
			{
				try
				{
					if (st.executeUpdate() == 1)
					{
						result = true;
						if (getRows() != null && !getRows().isEmpty())
						{
							getRows().remove(id);
						}
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return result;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL get">
	@Override
	public Collection<PictureRow> getAll() throws SQLException
	{
		PreparedStatement st = Database.prepareStatement(SQL_GET_ALL);

		synchronized (getRows())
		{
			if (!_allCached)
			{
				try
				{
					ResultSet query = st.executeQuery();
					while (query.next())
					{
						PictureRow pictureRow = new PictureRow(query);
						getRows().put(pictureRow.getPrimaryKeys(), pictureRow);
					}
					_allCached = true;
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
			return getRows().values();
		}
	}

	@Override
	public PictureRow getByPrimaryKey(Integer id) throws SQLException
	{
		PictureRow out = null;
		if (id >= 0)
		{
			synchronized (getRows())
			{
				out = getRows().get(id);
				if (out != null || _allCached)
				{
					return out;
				}

				PreparedStatement st = Database.prepareStatement(SQL_GET_BY_ID);
				st.setInt(1, id);

				try
				{
					ResultSet query = st.executeQuery();

					if (query.next())
					{
						out = new PictureRow(query);
						put(out);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return out;
	}

	public PictureRow getByPath(String path) throws SQLException
	{
		PictureRow out = null;
		if (path != null
				&& !path.trim().isEmpty())
		{
			synchronized (getRows())
			{
				for (PictureRow r : getRows().values())
				{
					if (r.getPath().equals(path))
					{
						out = r;
						break;
					}
				}
				if (out != null || _allCached)
				{
					return out;
				}

				PreparedStatement st = Database.prepareStatement(SQL_GET_BY_PATH);
				st.setString(1, path);

				try
				{
					ResultSet query = st.executeQuery();

					if (query.next())
					{
						out = new PictureRow(query);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return out;
	}
	//</editor-fold>
	//</editor-fold>
}
